<!DOCTYPE html>

<html>
<head>
  <title>excel批量处理</title>
  <meta name="renderer" content="webkit">
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  <link rel="stylesheet" href="/static/css/layui.css"  media="all">
  <style type="text/css">
    *,body {
      margin: 0px;
      padding: 0px;
    }

    body {
      margin: 0px;
      font-family: "Helvetica Neue", Helvetica, Arial, sans-serif;
      font-size: 14px;
      line-height: 20px;
      background-color: #fff;
    }

    header,
    footer {
      width: 960px;
      margin-left: auto;
      margin-right: auto;
    }

    header {
      padding: 100px 0;
    }

    footer {
      line-height: 1.8;
      text-align: center;
      padding: 50px 0;
      color: #999;
    }

    .description {
      text-align: center;
      font-size: 16px;
    }

    a {
      color: #444;
      text-decoration: none;
    }

    .backdrop {
      position: absolute;
      width: 100%;
      height: 100%;
      box-shadow: inset 0px 0px 100px #ddd;
      z-index: -1;
      top: 0px;
      left: 0px;
    }
  </style>
</head>

<body>
  <fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
  <legend>Excel数据处理</legend>
  </fieldset>

<div class="layui-upload">
  <button type="button" class="layui-btn layui-btn-normal" id="testList">批量导入文件</button>
  <div class="layui-upload-list">
    <table class="layui-table" id="test">
      <thead>
      <tr>
        <th><input type="checkbox" class="layui-checkallfile" id="checkbox-allfile"/>文件名</th>
        <th>大小</th>
        <th>状态</th>
        <th>字段</th>
        <th>操作</th>
      </tr>
      </thead>
      <tbody id="demoList"></tbody>
    </table>
  </div>

<form class="layui-form" action="">
    <div class="layui-form-item">
      <input type="radio" name="operate" value="uniq" title="去重" checked="">
      <input type="radio" name="operate" value="intersection" title="碰撞">
      <button type="button" class="layui-btn" id="handleListAction">处理</button>
      <blockquote class="layui-elem-quote" id="taskHint">任务提示</blockquote>
    </div>
</form>


<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>任务执行结果</legend>
</fieldset>

<div class="layui-task">
<table id="task" class="layui-table"> 
      <thead>
      <tr>
        <th>任务内容</th>
        <th>状态</th>
        <th>操作</th>
      </tr>
      </thead>
      <tbody id="taskList"></tbody>
</table>
</div>

<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
<legend>预览处理结果</legend>
</fieldset>
<div id="result" contenteditable></div>
<table id="result-view" lay-filter="result_table"></table>

</div>
  <footer>
    <div class="author">
    </div>
  </footer>
  <div class="backdrop"></div>

  <script type="text/javascript" src="/static/js/jquery-3.4.1.min.js"></script>
  <script type="text/javascript" src="/static/js/xlsx.full.min.js"></script>
  <script src="/static/js/reload.min.js"></script>
  <script src="/static/layui.js" charset="utf-8"></script>

<script type="text/html" id="barLocation">
  <a class="layui-btn layui-btn-primary layui-btn-xs" lay-event="detail">查看手机号归属地</a>
</script>

<script>
layui.use(['upload', 'form', 'table'], function(){
  var $ = layui.jquery
  ,upload = layui.upload
  ,form = layui.form
  ,table = layui.table;

  //多文件列表示例
  var demoListView = $('#demoList')
  ,uploadListIns = upload.render({
    elem: '#testList'
    ,url: 'http://127.0.0.1:8080/pushFile.do' //改成您自己的上传接口
    ,accept: 'file'
    ,multiple: true
    ,auto: false
    ,bindAction: '#testListAction'
    ,choose: function(obj){
      var files = this.files = obj.pushFile(); //将每次选择的文件追加到文件队列
      //读取本地文件
      obj.preview(function(index, file, result){
        var tr = $(['<tr id="upload-'+ index +'">'
          ,'<td> <input type="checkbox" id="checkbox-file" /input>' + file.name + '</td>'
          ,'<td>'+ (file.size/1024).toFixed(1) +'kB</td>'
          ,'<td>等待处理</td>'
          ,'<td>请执行解析</td>' 
		  ,'<td>'
          ,'<button class="layui-btn layui-btn-xs demo-reload ">解析</button>' 
          ,'<button class="layui-btn layui-btn-xs layui-btn-danger demo-delete">删除</button>'
          ,'</td>'
        ,'</tr>'].join(''));


		if(!/\.xlsx$/g.test(file.name) && !/\.csv$/g.test(file.name)) {
				alert('仅支持读取xlsx或者csv格式！');
				return;
		}
		
        //单个上传解析
        tr.find('.demo-reload').on('click', function(){
          var tr = demoListView.find('tr#upload-'+ index)
            ,tds = tr.children();
          tds.eq(2).html('<span style="color: #5FB878;">正在解析...</span>');
          tds.eq(3).html('正在解析...');
          obj.upload(index, file)
        });

        //删除
        tr.find('.demo-delete').on('click', function(){
          delete files[index]; //删除对应的文件
          tr.remove();
          uploadListIns.config.elem.next()[0].value = ''; //清空 input file 值，以免删除后出现同名文件不可选
        });
		
        demoListView.append(tr);

      });
    }
    ,done: function(res, index, upload){
      if(res.files.file){ //上传成功
        var tr = demoListView.find('tr#upload-'+ index)
        , tds = tr.children();
        tds.eq(2).html('<span style="color: #5FB878;">处理成功</span>');
		var cols = ''; 
　　    for(var i=0; i<res.fields.length; i++) {
			cols += '<input type="checkbox" id="checkbox-field" title="' + 
      			res.fields[i] + '"/>' + res.fields[i] + '<br>';
　　	}
        tds.eq(3).html(cols); 
        return delete this.files[index]; //删除文件队列已经上传成功的文件
      }
      this.error(index, upload);
    }
    ,error: function(index, upload){
      var tr = demoListView.find('tr#upload-'+ index)
      ,tds = tr.children();
      tds.eq(2).html('<span style="color: #FF5722;">处理失败</span>');
    }
  });
  
  //绑定原始文件域
  upload.render({
    elem: '#test20'
    ,url: 'http://127.0.0.1:8080/pushFile.do' //改成您自己的上传接口
    ,done: function(res){
      layer.msg('上传成功');
      console.log(res)
    }
  });

  //form.render();
});

var tasksMap = new Map();

layui.use('table', function(){
  var table = layui.table;

  table.on('tool(result_table)', function(obj){
    var data = obj.data;
    if(obj.event === 'detail'){
 	  for (var key in data) {
		if (key.indexOf("手机号") >= 0 ) {
			queryReq = {cmd: 'simpleLocation', number: data[key]};
			$.post('command.do', queryReq, function(t, s) {
	  			var retCmd = JSON.parse(t);
      			if ( retCmd.Result != "OK" ) {
					layer.msg('获取手机号归属地失败：' + retCmd.Result)
        			return;
      			}
      		    layer.alert(key + ' : ' + data[key] + ' 归属地是 ' + retCmd.location);
			});
			break;
		}
	  }
    }   
	});
  
  function getBodyView() {
	var demoListView = $('#demoList');
	bodyView = demoListView["0"];
  
    return bodyView;
  }
  
  function getRowsCount() {
    return getBodyView().rows.length;
  }
  
  function getTableDown(i, j) {
	tr = getBodyView().rows[i];
	tds = tr.children;
	td = tds[j];
  
    return td;
  }
  
  function getCheckBox(i) {
	cbox = getTableDown(i, 0).children; 
  
    return cbox[0];
  }
  
  function setAllCheckBoxValue(value) {
    for(var i=0; i<getRowsCount(); i++) {
      cbox = getCheckBox(i);
      cbox.checked = value;
    }
  }
  
  function getSelectFields(i) {
    sel = getTableDown(i, 3).children;
	var retFields = [];
	 
	for (var i = 0; i < sel.length; i+=2) {
		if (sel[i].checked)
		{
			retFields.push(sel[i].title);	
		}
	}
      
    return retFields;
  }
  
  function getSelectLabel(fields) {
	var retStr ='';
	
	for (var key in fields) {
		if (key == 0) {
			retStr = fields[key];
		} else {
			retStr += ',' + fields[key];	
		}
	}	
	
  	return retStr;
  }
  
  function getOperateRadio() {
	oper = $("input[name='operate']:checked"); 
    return oper[0]; 
  }
  
  function isTaskExist(taskContent) {
    bret = false;
	
    if (tasksMap.get(taskContent) == 522) {
      bret = true;
    }     
	
    return bret;
  }

  function addTaskMap(taskContent) {
      tasksMap.set(taskContent, 522);
  }
  
  function resetTaskMap(taskContent) {
      tasksMap.set(taskContent, 301);
  }
  
  function sendCommand(queryReq, taskContent) {
  	var taskListView = $('#taskList');
    // show working status table
    var taskHintView = $('#taskHint');
    taskHintView.html("任务提示：" + taskContent + " 正在处理中，请耐心等待");
	// send opreate command to server
    $.post('command.do', queryReq, function(t, s) {
	  console.log(t)
	  var retCmd = JSON.parse(t);
      if ( retCmd.Result != "OK" ) {
    	taskHintView.html('任务提示：' + taskContent + ' 执行失败：' + retCmd.Result);
        resetTaskMap(taskContent);
        return;
      }
      
      taskHintView.html("任务提示：" + taskContent + " 执行成功");
      // get return json and render in table
	  var trv = $(['<tr id="task_row_' + retCmd.ComputeID.toString() + '">'
	  ,'<td>' + taskContent + '</td>'
	  ,'<td>处理成功，记录数：' + retCmd.Records + '</td>' 
	  ,'<td>'
	  ,'<button class="layui-btn layui-btn-xs task-view">查看</button>' 
	  ,'<button class="layui-btn layui-btn-xs task-create">导出excel</button>' 
	  ,'</td>'
	  ,'</tr>'].join(''));
			
      trv.find('.task-view').on('click', function(){
		fieldCol = [];
		fs = JSON.parse(retCmd.Fields);
		for (var i = 0; i < fs.length; i++) {
			head = {field: fs[i], title: fs[i]};	
			fieldCol.push(head);
		}
		head = {fixed: 'right', align:'center', toolbar: '#barLocation'}
		fieldCol.push(head);
		
        table.render({
		  elem: '#result-view'
		  ,url: retCmd.UrlSheet
		  ,cellMinWidth: 80 //全局定义常规单元格的最小宽度，layui 2.2.1 新增
		  ,cols: [fieldCol]
		  ,page:true
  		});
      });
          
      trv.find('.task-create').on('click', function(){
		// get sheet data from server
		reqUrl = retCmd.UrlSheet + '&page=0&limit=0';
		$.get(reqUrl, function(res) {
			saveCmd = JSON.parse(res)
			if ( saveCmd.code != 0 ) {
				alert(taskContent + " 文件保存失败：" + saveCmd.msg );
				return;
			}
			
			var sheet = XLSX.utils.json_to_sheet(saveCmd.data, JSON.parse(retCmd.Fields));
			var blob = sheet2blob(sheet);
			
      		// save data to excel file  
			saveName = taskContent + '.xlsx';
			openDownloadDialog(blob, saveName);
		});
      });
          
      taskListView.append(trv);
	});
  }
  
  class TableLooper{
    constructor() {
      this.ctd = null;
      this.std = null;
      this.fs = null;
      this.cbox = null;
      this.isCheck = false; 
    }
    
    loopTableElement(doWork) {
		for (var i = 0; i < getRowsCount(); i++) {
			this.ctd = getTableDown(i, 0);  
    	    this.std = getTableDown(i, 3); 
            this.fs = getSelectFields(i);
            this.cbox = getCheckBox(i);
        
            doWork(this);
    	}
      
        return this.isCheck;
    }
  }
  
  function sendUniqCommand(oper) {
    var loop = new TableLooper();
    retcheck = loop.loopTableElement(function(obj){
		if (obj.cbox.checked) {
			obj.isCheck = true;
            
			if (obj.fs.length == 0)
			{
				return;
			}
			
            taskContent = obj.ctd.innerText + '-' + oper.title + '-[' + getSelectLabel(obj.fs) + ']';
            
            if (isTaskExist(taskContent)) {
              return;
            } else {
				addTaskMap(taskContent);
			}
            
    		queryReq = {cmd: oper.value, name: obj.ctd.innerText, field: obj.fs.toString()};
            sendCommand(queryReq, taskContent);
		} else {
			console.log('no check');
		}
    });
    
	if ( !retcheck ) {
		alert('未选中任何文件');
	}
  }
  
  function sendIntersectionCommand(oper) {
	var isCheck = false;
    var taskContent = ''; 
    var taskField = [];
    
    var count = 0;
    var loop = new TableLooper();
    retcheck = loop.loopTableElement(function(obj){
		if (obj.cbox.checked) {
            
			if (obj.fs.length == 0)
			{
				return;
			}
			
			obj.isCheck = true;
      
            taskContent += obj.ctd.innerText + '-[' + getSelectLabel(obj.fs) + '];';
      		elem = {name: obj.ctd.innerText, fields: obj.fs};
            taskField[count] = elem;
            count++;
		} else {
			console.log('no check');
		}
    });
  
	if (!retcheck) {
		alert('未选中任何文件或字段');
	} else {
    	taskContent += oper.title;
  
    	if (isTaskExist(taskContent)) {
    		return;
    	} else {
			addTaskMap(taskContent);
		}
    
    	queryReq = {cmd: oper.value, field: JSON.stringify(taskField)};
    	sendCommand(queryReq, taskContent);
  	}
  }
  
  function sendLocationCommand(oper) {
	var isCheck = false;
    var taskContent = ''; 
    var taskField = [];
    
    var count = 0;
    var loop = new TableLooper();
    retcheck = loop.loopTableElement(function(obj){
		if (obj.cbox.checked) {
            
			if (obj.fs.length == 0)
			{
				return;
			}
			
			obj.isCheck = true;
      
            taskContent += obj.ctd.innerText + '-[' + getSelectLabel(obj.fs) + '];';
      		elem = {name: obj.ctd.innerText, fields: obj.fs};
            taskField[count] = elem;
            count++;
		} else {
			console.log('no check');
		}
    });
  
	if (!retcheck) {
		alert('未选中任何文件或字段');
	} else {
    	taskContent += oper.title;
  
    	if (isTaskExist(taskContent)) {
    		return;
    	} else {
			addTaskMap(taskContent);
		}
    
    	queryReq = {cmd: oper.value, field: JSON.stringify(taskField)};
    	sendCommand(queryReq, taskContent);
  	}
  }
  
  $('.layui-form-item .layui-btn').on('click', function(){
    var oper = getOperateRadio();  
    
    if (oper.value == "intersection") {
    	sendIntersectionCommand(oper);  
    } else if (oper.value == "uniq") {
    	sendUniqCommand(oper);  
    } else if (oper.value == "location") {
        sendUniqCommand(oper);    
    }
  });
  
  $('.layui-upload-list .layui-checkallfile').on('change', function(){
    if (this.checked) {
    	// set all file checked  
    	setAllCheckBoxValue(true);      
    } else {
     	// disable all file checked 
    	setAllCheckBoxValue(false);      
    }
  });

  $('.result-view .layui-btn').on('click', function(){
    var type = $(this).data('type');
    active[type] ? active[type].call(this) : '';
  });

});
</script>

<script type="text/javascript">
// 读取本地excel文件
function readWorkbookFromLocalFile(file, callback, tds) {
	var reader = new FileReader();
	reader.onload = function(e) {
		var data = e.target.result;
		var workbook = XLSX.read(data, {type: 'binary'});
		if(callback) callback(workbook, tds);
	};
	reader.readAsBinaryString(file);
}

// 从网络上读取某个excel文件，url必须同域，否则报错
function readWorkbookFromRemoteFile(url, callback) {
	var xhr = new XMLHttpRequest();
	xhr.open('get', url, true);
	xhr.responseType = 'arraybuffer';
	xhr.onload = function(e) {
		if(xhr.status == 200) {
			var data = new Uint8Array(xhr.response)
			var workbook = XLSX.read(data, {type: 'array'});
			if(callback) callback(workbook);
		}
	};
	xhr.send();
}

// 读取 excel文件
function outputWorkbook(workbook) {
	var sheetNames = workbook.SheetNames; // 工作表名称集合
	sheetNames.forEach(name => {
		var worksheet = workbook.Sheets[name]; // 只能通过工作表名称来获取指定工作表
		for(var key in worksheet) {
			// v是读取单元格的原始值
			console.log(key, key[0] === '!' ? worksheet[key] : worksheet[key].v);
		}
	});
}

function readWorkbook(workbook, tds) {
	var sheetNames = workbook.SheetNames; // 工作表名称集合
	var worksheet = workbook.Sheets[sheetNames[0]]; // 这里我们只读取第一张sheet
	var json = XLSX.utils.sheet_to_json(worksheet);

	if (json) {
		getSheetFields(json, tds);
	} else {
        tds.eq(2).html('<span style="color: #FF5722;">处理失败</span>');
    	tds.eq(3).html('');
	}
}

function getSheetFields(json, tds) {
	var cols = ''; 
	var idx = 0;
  	var fields = [];

　　for(var key in json[0]) {
		cols += '<input type="checkbox" id="checkbox-field" title="' + key + '"/>' + key + '<br>';
    	fields.push(key);
　　}
	
    tds.eq(3).html(cols);
	
	// send all excel sheet to server
	pushExcelItems(json, tds, fields);
}

function pushExcelItems(json, tds, fields) {
	// request for create new table
	var creatReq = {cmd: 'create', name: tds["0"].innerText, 
  		fields: JSON.stringify(fields), content: JSON.stringify(json)};
	
    $.post('push.do', creatReq, function(t, s) {
			if ( t == 'OK' ) {
          		tds.eq(2).html('<span style="color: #5FB878;">解析成功，记录数：' 
              				  + json.length + '</span>');
			} else {
      			tds.eq(2).html('<span style="color: #FF5722;">创建失败:'+ t + '</span>');
			}
		}
	);
}

// 将csv转换成表格
function csv2table(csv)
{
	var html = '<table>';
	var rows = csv.split('\n');
	rows.pop(); // 最后一行没用的
	rows.forEach(function(row, idx) {
		var columns = row.split(',');
		columns.unshift(idx+1); // 添加行索引
		if(idx == 0) { // 添加列索引
			html += '<tr>';
			for(var i=0; i<columns.length; i++) {
				html += '<th>' + (i==0?'':String.fromCharCode(65+i-1)) + '</th>';
			}
			html += '</tr>';
		}
		html += '<tr>';
		columns.forEach(function(column) {
			html += '<td>'+column+'</td>';
		});
		html += '</tr>';
	});
	html += '</table>';
	return html;
}

function table2csv(table) {
	var csv = [];
	$(table).find('tr').each(function() {
		var temp = [];
		$(this).find('td').each(function() {
			temp.push($(this).html());
		})
		temp.shift(); // 移除第一个
		csv.push(temp.join(','));
	});
	csv.shift();
	return csv.join('\n');
}

// csv转sheet对象
function csv2sheet(csv) {
	var sheet = {}; // 将要生成的sheet
	csv = csv.split('\n');
	csv.forEach(function(row, i) {
		row = row.split(',');
		if(i == 0) sheet['!ref'] = 'A1:'+String.fromCharCode(65+row.length-1)+(csv.length-1);
		row.forEach(function(col, j) {
			sheet[String.fromCharCode(65+j)+(i+1)] = {v: col};
		});
	});
	return sheet;
}

	// 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
	function sheet2blob(sheet, sheetName) {
		sheetName = sheetName || 'sheet1';
		var workbook = {
			SheetNames: [sheetName],
			Sheets: {}
		};
		workbook.Sheets[sheetName] = sheet;
		// 生成excel的配置项
		var wopts = {
			bookType: 'xlsx', // 要生成的文件类型
			bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
			type: 'binary'
		};
		var wbout = XLSX.write(workbook, wopts);
		var blob = new Blob([s2ab(wbout)], {type:"application/octet-stream"});
		// 字符串转ArrayBuffer
		function s2ab(s) {
			var buf = new ArrayBuffer(s.length);
			var view = new Uint8Array(buf);
			for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
			return buf;
		}
		return blob;
	}

	/**
	 * 通用的打开下载对话框方法，没有测试过具体兼容性
	 * @param url 下载地址，也可以是一个blob对象，必选
	 * @param saveName 保存文件名，可选
	 */
	function openDownloadDialog(url, saveName)
	{
		if(typeof url == 'object' && url instanceof Blob)
		{
			url = URL.createObjectURL(url); // 创建blob地址
		}
		var aLink = document.createElement('a');
		aLink.href = url;
		aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
		var event;
		if(window.MouseEvent) event = new MouseEvent('click');
		else
		{
			event = document.createEvent('MouseEvents');
			event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
		}
		aLink.dispatchEvent(event);
	}


	$(function() {
		document.getElementById('handleListAction').addEventListener('change', function(e) {
			var files = e.target.files;
			if(files.length == 0) return;
			var f = files[0];
			if(!/\.xlsx$/g.test(f.name)) {
				alert('仅支持读取xlsx格式！');
				return;
			}
			readWorkbookFromLocalFile(f, function(workbook) {
				readWorkbook(workbook);
			});
		});
		//loadRemoteFile('./sample/test.xlsx');
	});

	function loadRemoteFile(url) {
		readWorkbookFromRemoteFile(url, function(workbook) {
			readWorkbook(workbook);
		});
	}

	function exportExcel() {
		var csv = table2csv($('#result table')[0]);
		var sheet = csv2sheet(csv);
		var blob = sheet2blob(sheet);
		openDownloadDialog(blob, '导出.xlsx');
	}

	function exportSpecialExcel() {
		var aoa = [
			['主要信息', null, null, '其它信息'], // 特别注意合并的地方后面预留2个null
			['姓名', '性别', '年龄', '注册时间'],
			['张三', '男', 18, new Date()],
			['李四', '女', 22, new Date()]
		];
		var sheet = XLSX.utils.aoa_to_sheet(aoa);
		sheet['!merges'] = [
			// 设置A1-C1的单元格合并
			{s: {r: 0, c: 0}, e: {r: 0, c: 2}}
		];
		openDownloadDialog(sheet2blob(sheet), '单元格合并示例.xlsx');
	}
  //<button type="button" class="layui-btn layui-btn-normal" id="testList">导出excel</button>
</script>
</body>
</html>
